<?php
/**
 * Created by PhpStorm.
 * User: hp
 * Date: 2017/11/22
 * Time: 14:15
 */

namespace App\Modules\Channel\Models;

use DB;
use Illuminate\Database\Eloquent\Model;

class ChannelDailyStat extends Model
{
    protected $table = 'promotion_groups_channel_day_stats';
    protected $fillable = [
        'id',
        'date',
        'distribution_channel_id',
        'official_account_name',
        'company_name',
        'cost',
        'charge_amount',
        'total_subscribe_num',
        'new_subscribe_num',
        'cancel_subscribe_num',
        'cancel_subscribe_rate',
        'promotion_point',
        'agent_name',
        'created_at',
        'updated_at',
        'appid'
    ];

    /**
     * 添加条目
     * @param $data 条目
     * @return mixed
     */
    static function addItem($data)
    {
        return self::create($data);
    }

    /**
     * 获取派单信息
     * @param array $params 参数数组
     * @param boolean $isAll 是否获取所有
     * @return mixed
     */
    static function search($params, $isAll = false)
    {
        $search_object = self::select(DB::raw('*'));

        if (isset($params['agent_names']) && $params['agent_names']) {
            $search_object->whereIn('agent_name', $params['agent_names']);
        }

        if (isset($params['promotion_point']) && $params['promotion_point']) {
            $search_object->where('promotion_point', trim($params['promotion_point']));
        }

        if (isset($params['start_date']) && $params['start_date']) {
            $search_object->where('date', '>=', $params['start_date']);
        }

        if (isset($params['end_date']) && $params['end_date']) {
            $search_object->where('date', '<=', $params['end_date']);
        }

        if (isset($params['distribution_channel_ids']) && $params['distribution_channel_ids']) {
            $search_object->whereIn('distribution_channel_id', $params['distribution_channel_ids']);
        }

        if (isset($params['orderBy']) && $params['orderBy']) {
            $orderType = 'desc';
            if (isset($params['orderType']) && $params['orderType']) {
                $orderType = $params['orderType'];
            }
            $search_object->orderBy($params['orderBy'], $orderType);
            //\Log::info($search_object->toSql());
        } else {
            $search_object->orderBy('cost', 'desc');
        }

        if ($isAll) {
            return $search_object->get();
        } else {
            return $search_object->paginate(49);
        }
    }

    static function getDailySum($params)
    {
        //$search_object = self::select(DB::raw('*'));
        $filter = "1 ";
        if (isset($params['agent_names']) && $params['agent_names']) {
            $filter.=' AND t.agent_name IN ('.implode(',',$params['agent_names']).')';
        }

        if (isset($params['promotion_point']) && $params['promotion_point']) {
            $filter.=" AND t.promotion_point = '".$params['promotion_point']."'";
        }

        if (isset($params['start_date']) && $params['start_date']) {
            $filter.=" AND t.date >= '".$params['start_date']."'";
        }else{
            $filter.=" AND t.date >= '".date('Y-m-d',strtotime('-1 day'))."'";
        }

        if (isset($params['end_date']) && $params['end_date']) {
            $filter.=" AND t.date <= '".$params['end_date']."'";
        }else{
            $filter.=" AND t.date <= '".date('Y-m-d',strtotime('-1 day'))."'";
        }

        if (isset($params['distribution_channel_ids']) && $params['distribution_channel_ids']) {
            $filter.=" AND t.distribution_channel_id IN (".implode(',',$params['distribution_channel_ids']).")";
        }

        $sql = "SELECT distribution_channel_id,SUM(cost) as cost,SUM(charge_amount) as charge_amount,SUM(total_subscribe_num) as total_subscribe_num,
  SUM(new_subscribe_num) as new_subscribe_num,SUM(cancel_subscribe_num) as cancel_subscribe_num,SUM(cancel_subscribe_num)/SUM(total_subscribe_num) as cancel_subscribe_rate
   FROM 
  (SELECT * FROM promotion_groups_channel_day_stats GROUP BY date,distribution_channel_id) t WHERE $filter";
        $res = DB::select($sql);
        \Log::info($res);
        return $res;
    }

    public static function getMonthData($params, $isAll = false)
    {
        $search_object = self::orderBy(DB::raw('YEAR(date)'), 'desc')->orderBy(DB::raw('MONTH(date)'), 'desc');

        if (isset($params['start_date']) && $params['start_date']) {
            $search_object->where('date', '>=', $params['start_date']);
        }
        if (isset($params['end_date']) && $params['end_date']) {
            $search_object->where('date', '<=', $params['end_date']);
        }

        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) {
            $search_object->where('distribution_channel_id', $params['distribution_channel_id']);
        }

        if (isset($params['agent_name']) && $params['agent_name']) {
            $search_object->where('agent_name', 'like', '%' . $params['agent_name'] . '%');
        }

        if (isset($params['promotion_point']) && $params['promotion_point']) {
            $search_object->where('promotion_point', $params['promotion_point']);
        }

        if (isset($params['orderBy']) && $params['orderBy']) {
            $orderType = 'desc';
            if (isset($params['orderType']) && $params['orderType']) {
                //1为正序
                $orderType = $params['orderType'];
            }
            $search_object->orderBy($params['orderBy'], $orderType);
        }

        $search_object->select(DB::raw('left(date,7) month'),
            DB::raw('min(new_subscribe_num) total_new_subscribe_num'),
            DB::raw('max(total_subscribe_num) total_subscribe_num'),
            'distribution_channel_id', 'promotion_point', 'agent_name');
        $search_object->groupBy(DB::raw('left(date,7)'));
        $search_object->groupBy('distribution_channel_id');
        \Log::info($search_object->toSql());
        if ($isAll) {
            return $search_object->get();
        } else {
            return $search_object->paginate();
        }
    }

    public static function getList($date)
    {
        return self::where('date', $date)->get();
    }

    public static function getListData($start_date, $end_date)
    {
        return self::where('date', '>=', $start_date)->where('date', '<=', $end_date)->get();
    }
}
